const express = require("express");
const router = express.Router();
const bcrypt = require("bcryptjs");
const db = require("../sql");
const { generateToken } = require("../utils/jwt");
// 获取用户列表（分页）
router.get("/", async (req, res) => {
  const { page = 1, limit = 10 } = req.query;
  // 分页获取用户信息，返回totalCount
  const offset = (page - 1) * limit;
  const sql = "SELECT * FROM user LIMIT ? OFFSET ?";
  const users = await db.query(sql, [parseInt(limit), parseInt(offset)]);
  const totalCount = await db.query("SELECT COUNT(*) AS total FROM user");
  res.json({
    success: true,
    data: {
      users,
      totalCount: totalCount[0].total,
    },
  });
});

// 更新用户
router.put("/", async (req, res) => {
  const { userName, email, avatar, id } = req.body;
  try {
    const sql =
      "UPDATE user SET userName = ?,  email = ?, avatar = ? WHERE id = ?";
    await db.query(sql, [userName, email, avatar, id]);
    res.json({ message: "User updated" });
  } catch (error) {
    res.status(500).json({ message: error.message });
  }
});

// 修改密码
router.put("/editPassword/:id", async (req, res) => {
  const { id } = req.params;
  const { password, oldPassword } = req.body;
  try {
    // 验证旧密码
    const user = await db.query("SELECT * FROM user WHERE id = ?", [id]);
    if (!user || !(await bcrypt.compare(oldPassword, user[0].password))) {
      return res.json({ success: false, message: "旧密码错误" });
    }

    const hashedPassword = await bcrypt.hash(password, 8);
    const sql = "UPDATE user SET password = ? WHERE id = ?";
    await db.query(sql, [hashedPassword, id]);
    res.json({ success: true, message: "Password updated" });
  } catch (error) {
    res.status(500).json({ message: error.message });
  }
});

// 用户注册
router.post("/register", async (req, res) => {
  const { userName, password, email, avatar } = req.body;
  try {
    const hashedPassword = await bcrypt.hash(password, 8);
    const sql =
      "INSERT INTO user (userName, password, email, avatar) VALUES (?, ?, ?, ?)";
    const result = await db.query(sql, [
      userName,
      hashedPassword,
      email,
      avatar,
    ]);
    res.status(201).json({
      success: true,
      message: "User created",
    });
  } catch (error) {
    res
      .status(500)
      .json({ message: "Unable to register user: " + error.message });
  }
});

// 用户登录
router.post("/login", async (req, res) => {
  const { userName, password } = req.body;
  try {
    const sql = "SELECT * FROM user WHERE userName = ?";
    const [user] = await db.query(sql, [userName]);
    if (user && (await bcrypt.compare(password, user.password))) {
      res.json({
        success: true,
        message: "登陆成功",
        data: {
          id: user.id,
          userName: user.userName,
          avatar: user.avatar,
          token: generateToken(user.userName),
          email: user.email,
          createTime: user.createTime,
        },
      });
    } else {
      res.json({ success: false, message: "密码错误" });
    }
  } catch (error) {
    res.status(500).json({ message: "Login error: " + error.message });
  }
});

router.get("/totalUsers", async (req, res) => {
  const sql = "SELECT COUNT(*) AS totalUsers FROM user";
  try {
    const result = await db.query(sql);
    res.json({ data: result[0].totalUsers, success: true });
  } catch (err) {
    res.status(500).json({ error: err.message, success: false });
  }
});
router.get("/registrationsByDay", async (req, res) => {
  const sql = `
      SELECT 
        DATE_FORMAT(date_sequence.date, '%Y-%m-%d') AS day,
        COUNT(u.id) AS dailyRegistrations
      FROM (
        SELECT CURDATE() - INTERVAL seq DAY AS date
        FROM (
          SELECT a.a + (10 * b.a) + (100 * c.a) AS seq
          FROM (
            SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
          ) a
          CROSS JOIN (
            SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
          ) b
          CROSS JOIN (
            SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
          ) c
        ) a
        WHERE seq < 30
      ) date_sequence
      LEFT JOIN user u ON DATE(u.createTime) = date_sequence.date
      GROUP BY date_sequence.date
      ORDER BY date_sequence.date ASC;
  `;
  try {
    const results = await db.query(sql);
    // 创建30天的日期和注册数数组
    const data = results.map((item) => ({
      day: item.day,
      dailyRegistrations: item.dailyRegistrations,
    }));
    res.json({ data: data, success: true });
  } catch (err) {
    res.status(500).json({ error: err.message, success: false });
  }
});

module.exports = router;
